package com.daxt.utils.file;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URL;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.daxt.common.exception.BaseException;
import com.daxt.common.result.ResultCode;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import com.daxt.common.util.DateUtil;
import com.daxt.common.util.DateUtil.DateFormat;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;


public class PoiUtil {


    @SuppressWarnings("resource")
    public static void exportByImg(List<String[]> data, String[] header, String sheetName, HttpServletResponse response,
                                   HttpServletRequest request) throws IOException {
        HSSFWorkbook workBook = new HSSFWorkbook();
        HSSFSheet sheet = workBook.createSheet(sheetName == null ? "data" : sheetName);
        sheet.setColumnWidth(0, 1000);
        sheet.setColumnWidth(1, 3000);
        sheet.setColumnWidth(2, 5000);
        sheet.setColumnWidth(3, 8000);
        sheet.setColumnWidth(4, 5000);
        sheet.setColumnWidth(5, 5000);
        sheet.setColumnWidth(6, 5000);
        HSSFRow headerRow = sheet.createRow(0);
        if (header != null) {

            for (int i = 0; i < header.length; i++) {
                headerRow.createCell(i).setCellValue(header[i]);
                headerRow.setHeightInPoints(20);
            }
        }
        if (data != null && data.size() > 0) {
            for (int i = 0; i < data.size(); i++) {
                int j = 0;
                HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
                dataRow.setHeightInPoints(15);
                for (; j < data.get(0).length; j++) {
                    if (j >= header.length) {
                        if (data.get(i)[j] != null) {
                            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                            BufferedImage bufferImg = ImageIO.read(new URL(data.get(i)[j]));
                            ImageIO.write(bufferImg, "PNG", byteArrayOut);
                            HSSFClientAnchor anchor = new HSSFClientAnchor(j, j, 255, 255, (short) j, sheet.getLastRowNum(), (short) j, sheet.getLastRowNum());
                            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                            patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                        }
                    } else {
                        try {
                            if (data.get(i)[j] != null) {
                                Double parseDouble = Double.parseDouble(data.get(i)[j]);
                                BigDecimal big = new BigDecimal(data.get(i)[j]);
                                parseDouble = big.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                                dataRow.createCell(j).setCellValue(parseDouble != null ? parseDouble : 0.00);
                            } else {
                                dataRow.createCell(j).setCellValue(data.get(i)[j] != null ? data.get(i)[j] : "");
                            }
                        } catch (NumberFormatException e) {
                            dataRow.createCell(j).setCellValue(data.get(i)[j] != null ? data.get(i)[j] : "");
                        }
                    }
                }
            }
        }

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("content-disposition",
                "attchment;filename=" + new String((DateUtil.getCurrentTime(DateFormat.YYYY_MM_DD) + ".xls").getBytes("UTF-8"), "ISO8859-1"));
        workBook.write(response.getOutputStream());
    }


    @SuppressWarnings("resource")
    public static void export(List<String[]> data, String[] header, String sheetName, HttpServletResponse response,
                              HttpServletRequest request) throws IOException {
        XSSFWorkbook workBook = new XSSFWorkbook();
        XSSFSheet sheet = workBook.createSheet(sheetName == null ? "data" : sheetName);
        sheet.setColumnWidth(0, 4000);
        sheet.setColumnWidth(1, 3000);
        sheet.setColumnWidth(2, 3000);
        sheet.setColumnWidth(3, 15000);
        sheet.setColumnWidth(4, 5000);
        sheet.setColumnWidth(5, 5000);
        sheet.setColumnWidth(6, 7000);
        XSSFRow headerRow = sheet.createRow(0);
        if (header != null) {

            for (int i = 0; i < header.length; i++) {
                XSSFCellStyle createCellStyle = workBook.createCellStyle();
                createCellStyle.setAlignment(HorizontalAlignment.CENTER);
                createCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                //headerRow.setRowStyle(createCellStyle);

                XSSFCell createCell = headerRow.createCell(i);
                createCell.setCellStyle(createCellStyle);
                createCell.setCellValue(header[i]);
                headerRow.setHeightInPoints(30);

            }
        }
        if (data != null && data.size() > 0) {
            for (int i = 0; i < data.size(); i++) {
                XSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
                dataRow.setHeightInPoints(15);
                for (int j = 0; j < data.get(i).length; j++) {
                    dataRow.createCell(j).setCellValue(data.get(i)[j] != null ? data.get(i)[j] : "");

                }
            }
        }
        String fileName = "temp.xls";
        String mimeType = request.getServletContext().getMimeType(fileName);
        //response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setContentType(mimeType);
        response.setHeader("content-disposition",
                "attchment;filename=" + new String((DateUtil.getCurrentTime(DateFormat.YYYY_MM_DD) + ".xls").getBytes("UTF-8"), "ISO8859-1"));
        workBook.write(response.getOutputStream());
    }

    @SuppressWarnings("resource")
    public static void exportD(List<String[]> data, String[] header, String sheetName, String path, String fileName) throws IOException {
        XSSFWorkbook workBook = new XSSFWorkbook();
        XSSFSheet sheet = workBook.createSheet(sheetName == null ? "data" : sheetName);
        sheet.setColumnWidth(0, 4000);
        sheet.setColumnWidth(1, 3000);
        sheet.setColumnWidth(2, 3000);
        sheet.setColumnWidth(3, 15000);
        sheet.setColumnWidth(4, 5000);
        sheet.setColumnWidth(5, 5000);
        sheet.setColumnWidth(6, 7000);
        XSSFRow headerRow = sheet.createRow(0);
        if (header != null) {

            for (int i = 0; i < header.length; i++) {
                XSSFCellStyle createCellStyle = workBook.createCellStyle();
                createCellStyle.setAlignment(HorizontalAlignment.CENTER);
                createCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                //headerRow.setRowStyle(createCellStyle);

                XSSFCell createCell = headerRow.createCell(i);
                createCell.setCellStyle(createCellStyle);
                createCell.setCellValue(header[i]);
                headerRow.setHeightInPoints(30);

            }
        }
        if (data != null && data.size() > 0) {
            for (int i = 0; i < data.size(); i++) {
                XSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
                dataRow.setHeightInPoints(15);
                for (int j = 0; j < data.get(i).length; j++) {
                    dataRow.createCell(j).setCellValue((data.get(i)[j] != null && !"------".equals(data.get(i)[j])) ? data.get(i)[j] : "");

                }
            }
        }
        //String fileName = "temp.xls";
        File file = new File(path + "/" + fileName + ".xls");
        file.createNewFile();
        FileOutputStream output = new FileOutputStream(file);
        workBook.write(output);
        output.flush();
        output.close();
        workBook.close();
    }


//    @SuppressWarnings("resource")
//    public static void exportList(Map<String, List<String[]>> data, String[] header, String[] sheetNames, HttpServletResponse response,
//                                  HttpServletRequest request) throws IOException {
//        XSSFWorkbook workBook = new XSSFWorkbook();
//        for (String sheetName : sheetNames) {
//            XSSFSheet sheet = workBook.createSheet(sheetName == null ? "data" : sheetName);
//            sheet.setColumnWidth(0, 1000);
//            sheet.setColumnWidth(1, 3000);
//            sheet.setColumnWidth(2, 5000);
//            sheet.setColumnWidth(3, 8000);
//            sheet.setColumnWidth(4, 5000);
//            sheet.setColumnWidth(5, 5000);
//            sheet.setColumnWidth(6, 5000);
//            for (int i = 0; i < sheetNames.length; i++) {
//                List<String[]> tempData = data.get(sheetName);
//                System.out.println(sheetName);
//                System.out.println(i);
//                XSSFRow headerRow = sheet.createRow(0);
//                if (header != null) {
//
//                    for (int j = 0; j < header.length; j++) {
//                        headerRow.createCell(j).setCellValue(header[j]);
//                        headerRow.setHeightInPoints(50);
//                    }
//                }
//                if (tempData != null && tempData.size() > 0) {
//
//                    for (int k = 0; k < tempData.size(); k++) {
//                        XSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
//                        dataRow.setHeightInPoints(15);
//                        for (int l = 0; l < tempData.get(k).length; l++) {
//                            dataRow.createCell(l).setCellValue(tempData.get(k)[l] != null ? tempData.get(k)[l] : "");
//
//                        }
//                    }
//                }
//            }
//
//        }
//
//
//        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//        response.setHeader("content-disposition",
//                "attchment;filename=" + new String((DateUtil.getCurrentTime(DateFormat.YYYY_MM_DD) + ".xls").getBytes("UTF-8"), "ISO8859-1"));
//        workBook.write(response.getOutputStream());
//    }


    /**
     * @description: 导入excele 的xlsx文件
     * 
     * @author: lig
     * @date: 2021/11/8
     */
    public static Map<String, List<String>> importXLSX(InputStream input, String flag) throws IOException {
        Map<String, List<String>> page = new HashMap<>();
        XSSFWorkbook workBook = new XSSFWorkbook(input);
        for (Sheet sheet : workBook) {
            XSSFSheet sheetAt = workBook.getSheet(sheet.getSheetName());
            List<String> list = new ArrayList<String>();
            for (Row row : sheetAt) {
                if (row == null) continue;

                int rowNum = row.getRowNum();

                if ("Y".equals(flag) && rowNum == 0) {
                    continue;
                }
                short lastCellNum = row.getLastCellNum();
                if(lastCellNum == -1)break ;//列数为空结束
                StringBuffer buffer = new StringBuffer();
                for (int i = 0; i < lastCellNum; i++) {
                    Cell cell = row.getCell(i);
                    if (null != cell) {
                        Object value = getCellValue(cell);
                        buffer.append(value).append("===");
                    }
                }
                list.add(buffer.toString().trim());
            }
            page.put(sheet.getSheetName(), list);
        }
        return page;
    }


    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (null != cell) {
            //以下是判断数据类型
            switch (cell.getCellType()) {
                case NUMERIC:
                    if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { //判断是否为日期类型
                        Date date = cell.getDateCellValue();
                        java.text.DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                        cellValue = formater.format(date);
                    } else {
                        DecimalFormat df = new DecimalFormat("####.####");
                        cellValue = df.format(cell.getNumericCellValue());
                    }
                    break;
                case STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN:
                    cellValue = cell.getBooleanCellValue() + "";
                    break;
                case BLANK: //空值
                    cellValue = "";
                    break;
                case ERROR:
                    cellValue = "非法字符";
                    break;
            }
        }
        return cellValue;
    }



    @SuppressWarnings("resource")
    public static Map<String, List<String>> ImportXLS(InputStream input, String flag) throws IOException {
        Map<String, List<String>> page = new HashMap<String, List<String>>();
        XSSFWorkbook workBook = new XSSFWorkbook(input);
        for (Sheet sheet : workBook) {
            XSSFSheet sheetAt = workBook.getSheet(sheet.getSheetName());
            List<String> list = new ArrayList<String>();
            int cellCount = 0;
            for (Row row : sheetAt) {
                int rowNum = row.getRowNum();
                if ("Y".equals(flag) && rowNum == 0) {
                    cellCount = row.getLastCellNum();
                    continue;
                }
                //short lastCellNum = row.getLastCellNum();
                StringBuffer buffer = new StringBuffer();
                for (int i = 0; i < cellCount; i++) {
                    if (null != row.getCell(i)) {
                        Object value = null;
                        CellType cellType = row.getCell(i).getCellTypeEnum();
                        if (cellType == CellType.NUMERIC) {

                            value = new Double(row.getCell(i).getNumericCellValue()).intValue();

                        } else if (cellType == CellType.FORMULA) {
                            value = row.getCell(i).getStringCellValue();
                        } else {
                            value = (null == row.getCell(i).getStringCellValue() ? ""
                                    : row.getCell(i).getStringCellValue());
                        }
                        if (i > 0)
                            buffer.append("zzzzz").append(StringUtils.isEmpty(value) ? "------" : value);
                        else
                            buffer.append(StringUtils.isEmpty(value) ? "------" : value);
                    } else {
                        if (i > 0)
                            buffer.append("zzzzz").append("------");
                        else
                            buffer.append("------");
                    }
                }
                list.add(buffer.toString().trim());
            }
            page.put(sheet.getSheetName(), list);
        }
        return page;
    }


    @SuppressWarnings("resource")
    public static Map<String, List<String>> ImportXLS1(InputStream input, String flag) throws IOException, BaseException {
        Map<String, List<String>> page = new HashMap<String, List<String>>();


        //edit by lig 2021-07-30 解决导入xls报错，读取excel修改为可同样导入。
        Workbook wb = null;
        FileInputStream fis = null;
        try {
//            fis = new FileInputStream(path);
            if(input instanceof FileInputStream){
                fis = (FileInputStream)input;
            }
            wb = WorkbookFactory.create(fis);
        } catch (EncryptedDocumentException | IOException e) {
            e.printStackTrace();
//            throw new RuntimeException("System hault,can't open file:"+path);
        }
//
        for (Sheet sheet : wb) {

            int first = sheet.getFirstRowNum();
            int last = sheet.getLastRowNum();
            List<String> list = new ArrayList<String>();
            int cellCount = 0;
            for(int v=first; v<=last;v++){
                //read row data
                Row row = sheet.getRow(v);
                if(row==null)continue;
                int rowNum = row.getRowNum();
                if ("Y".equals(flag) && rowNum == 0) {
                    cellCount=row.getLastCellNum();
                    continue;
                }
                //short lastCellNum = row.getLastCellNum();
                StringBuffer buffer = new StringBuffer();
                for (int i = 0; i < cellCount; i++) {
                    if (null != row.getCell(i)) {
                        Object value = null;
                        CellType cellType = row.getCell(i).getCellTypeEnum();
                        if (cellType == CellType.NUMERIC || cellType == CellType.FORMULA) {
                            value = new Double(row.getCell(i).getNumericCellValue()).intValue();
                        } else {
                            value = (null == row.getCell(i).getStringCellValue() ? ""
                                    : row.getCell(i).getStringCellValue());
                        }
                        if(i>0)
                            buffer.append("zzzzz").append(StringUtils.isEmpty(value)?"------":value);
                        else
                            buffer.append(StringUtils.isEmpty(value)?"------":value);
                    }else {
                        if(i>0)
                            buffer.append("zzzzz").append("------");
                        else
                            buffer.append("------");
                    }
                }
                int count=0;
                for(int i = 0;i<buffer.toString().split("zzzzz").length;i++){
                    if(buffer.toString().split("zzzzz")[i].equals("------")){
                        count++;
                    }
                }
                if(count<buffer.toString().split("zzzzz").length)
                    list.add(buffer.toString().trim());
            }
            page.put(sheet.getSheetName(), list);
    }


//		XSSFWorkbook workBook = null;
//		try {
//			workBook = new XSSFWorkbook(input);
//		}catch (Exception e){
//			e.printStackTrace();
//			throw new BaseException(ResultCode.RESULT_ERROR.getCode(),"excel版本过久，请尝试另存为xslx格式上传！");
//		}
//			for (Sheet sheet : workBook) {
//				XSSFSheet sheetAt = workBook.getSheet(sheet.getSheetName());
//				List<String> list = new ArrayList<String>();
//				int cellCount=0;
//				for (Row row : sheetAt) {
//					int rowNum = row.getRowNum();
//					if ("Y".equals(flag) && rowNum == 0) {
//						cellCount=row.getLastCellNum();
//						continue;
//					}
//					//short lastCellNum = row.getLastCellNum();
//					StringBuffer buffer = new StringBuffer();
//					for (int i = 0; i < cellCount; i++) {
//						if (null != row.getCell(i)) {
//							Object value = null;
//							CellType cellType = row.getCell(i).getCellTypeEnum();
//							if (cellType == CellType.NUMERIC || cellType == CellType.FORMULA) {
//								value = new Double(row.getCell(i).getNumericCellValue()).intValue();
//							} else {
//								value = (null == row.getCell(i).getStringCellValue() ? ""
//										: row.getCell(i).getStringCellValue());
//							}
//							if(i>0)
//							buffer.append("zzzzz").append(StringUtils.isEmpty(value)?"------":value);
//							else
//							buffer.append(StringUtils.isEmpty(value)?"------":value);
//						}else {
//							if(i>0)
//								buffer.append("zzzzz").append("------");
//								else
//								buffer.append("------");
//						}
//					}
//					int count=0;
//					for(int i = 0;i<buffer.toString().split("zzzzz").length;i++){
//						if(buffer.toString().split("zzzzz")[i].equals("------")){
//							count++;
//						}
//					}
//					if(count<buffer.toString().split("zzzzz").length)
//						list.add(buffer.toString().trim());
//				}
//				page.put(sheet.getSheetName(), list);
//			}

        return page;
    }


    public static void replaceExcel(InputStream is, Map params, List<Map> paramList, Integer index, HttpServletResponse response,
                                    HttpServletRequest request, File tempFile) throws IOException, InvalidFormatException {
        List<String> rheard = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(is);
        int indexRows = 0;
        Sheet sheet = wb.getSheetAt(0);
        int trLength = sheet.getLastRowNum();
        Cell copyCell = null;
        Row copyRow = null;
        for (int i = 0; i < trLength; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            int minColIx = row.getFirstCellNum();
            int maxColIx = row.getLastCellNum();
            for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                Cell cell = row.getCell(colIx);
                if (cell == null) {
                    continue;
                }
                String runText = cell.getStringCellValue();
                if (paramList != null && paramList.size() > 0 && index != null && i == index) {
                    for (int append = 0; append < paramList.size(); append++) {
                        Map map = paramList.get(append);
                        if (append == 0) {
                            indexRows = i;
                            if (!StringUtils.isEmpty(runText)) {
                                rheard.add(runText);
                                copyCell = cell;
                                copyRow = row;
                            }
                            Matcher matcher = matcher(runText);
                            if (matcher.find()) {
                                while ((matcher = matcher(runText)).find()) {
                                    runText = matcher.replaceFirst(String.valueOf(map.get(matcher.group(1)) == null ? "" : map.get(matcher.group(1))));
                                }
                                cell.setCellValue(runText);
                            }
                        }
                    }
                }

                if (runText.equals("")) {
                    continue;
                }

                Matcher matcher = matcher(runText);
                if (matcher.find()) {
                    // while ((matcher = matcher(runText)).find()) {
                    if (params != null)
                        runText = matcher.replaceFirst(String.valueOf(params.get(matcher.group(1)) == null ? "" : params.get(matcher.group(1))));
                    // }
                    cell.setCellValue(runText);
                }
            }
        }


        indexRows++;

        if (paramList != null) {
            for (int append = 1; append < paramList.size(); append++) {
                sheet.shiftRows(indexRows, sheet.getLastRowNum(), 1, true, false);
                Map map = paramList.get(append);
                Row targetRow = sheet.createRow(indexRows);

                targetRow.setHeight(copyRow.getHeight());
                targetRow.setRowStyle(copyRow.getRowStyle());
                indexRows++;
                for (int j = 0; j < rheard.size(); j++) {
                    Cell cell2 = targetRow.createCell(j);
                    String runText = "";
                    Matcher matcher = matcher(rheard.get(j));
                    if (matcher.find()) {
                        runText = matcher.replaceFirst(String.valueOf(map.get(matcher.group(1)) == null ? "" : map.get(matcher.group(1))));
                        CellStyle cellStyle = copyCell.getCellStyle();
                        cellStyle.setAlignment(HorizontalAlignment.CENTER);
                        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                        cell2.setCellStyle(copyCell.getCellStyle());

                        cell2.setCellValue(StringUtils.isEmpty(runText) ? "" : runText);
                    }
                }
            }
        }

        FileOutputStream out = new FileOutputStream(tempFile);
        wb.write(out);
    }


    public static void replaceExcel1(InputStream is, Map params, List<Map> paramList, Integer index, HttpServletResponse response,
                                     HttpServletRequest request, File tempFile) throws IOException, InvalidFormatException {
        List<String> rheard = new ArrayList<>();
        XSSFWorkbook wb = new XSSFWorkbook(is);

        int indexRows = 0;
        XSSFSheet sheet = wb.getSheetAt(0);
        int trLength = sheet.getLastRowNum();
        XSSFCell copyCell = null;
        XSSFRow copyRow = null;
        for (int i = 0; i < trLength; i++) {
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            int minColIx = row.getFirstCellNum();
            int maxColIx = row.getLastCellNum();
            for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                XSSFCell cell = row.getCell(colIx);
                if (cell == null) {
                    continue;
                }
                String runText = cell.getStringCellValue();
                if (paramList != null && paramList.size() > 0 && index != null && i == index) {
                    for (int append = 0; append < paramList.size(); append++) {
                        Map map = paramList.get(append);
                        if (append == 0) {
                            indexRows = i;
                            if (!StringUtils.isEmpty(runText)) {
                                rheard.add(runText);
                                copyCell = cell;
                                copyRow = row;
                            }
                            Matcher matcher = matcher(runText);
                            if (matcher.find()) {
                                while ((matcher = matcher(runText)).find()) {
                                    runText = matcher.replaceFirst(String.valueOf(map.get(matcher.group(1)) == null ? "" : map.get(matcher.group(1))));
                                }
                                cell.setCellValue(runText);
                            }
                        }
                    }
                }

                if (runText.equals("")) {
                    continue;
                }

                Matcher matcher = matcher(runText);
                if (matcher.find()) {
                    // while ((matcher = matcher(runText)).find()) {
                    if (params != null)
                        runText = matcher.replaceFirst(String.valueOf(params.get(matcher.group(1)) == null ? "" : params.get(matcher.group(1))));
                    // }
                    cell.setCellValue(runText);
                }
            }
        }


        indexRows++;

        if (paramList != null) {
            for (int append = 1; append < paramList.size(); append++) {
                sheet.shiftRows(indexRows, sheet.getLastRowNum(), 1, true, false);
                Map map = paramList.get(append);
                XSSFRow targetRow = sheet.createRow(indexRows);

                targetRow.setHeight(copyRow.getHeight());
                targetRow.setRowStyle(copyRow.getRowStyle());
                indexRows++;
                for (int j = 0; j < rheard.size(); j++) {
                    XSSFCell cell2 = targetRow.createCell(j);
                    String runText = "";
                    Matcher matcher = matcher(rheard.get(j));
                    if (matcher.find()) {
                        runText = matcher.replaceFirst(String.valueOf(map.get(matcher.group(1)) == null ? "" : map.get(matcher.group(1))));
                        CellStyle cellStyle = copyCell.getCellStyle();
                        cellStyle.setAlignment(HorizontalAlignment.CENTER);
                        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                        cell2.setCellStyle(copyCell.getCellStyle());

                        cell2.setCellValue(StringUtils.isEmpty(runText) ? "" : runText);
                    }
                }
            }
        }

        FileOutputStream out = new FileOutputStream(tempFile);
        wb.write(out);
    }

    private static Matcher matcher(String str) {
        Pattern pattern = Pattern.compile("\\{(.+?)\\}", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(str);
        return matcher;
    }


}
